﻿-- =============================================
-- Author:		Stocksdale, Jon
-- Create date: JUne 21, 2016
-- Description:	Fetches all External Relationships
-- =============================================
CREATE PROCEDURE [dbo].[ExternalRelationships]
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
	DECLARE @EXTVIEW nvarchar(max)
	DECLARE @COMBINEDVIEWS nvarchar(max)
	DECLARE @RELNAME nvarchar(100)
	DECLARE ExternalViewsCursor cursor for
	SELECT sqlraw, ShortRelationshipName FROM
	(SELECT
		SUBSTRING(RafikiNOSQL,CHARINDEX('{',RafikiNOSQL)+1,CHARINDEX('}',RafikiNOSQL)-CHARINDEX('{',RafikiNOSQL)-1)
		as extview,
		ShortRelationshipName
	 from DefinedEntityRelationships
	 ) AS X
	 JOIN DefinedExternalViews
		ON x.ExtView = DefinedExternalViews.ViewName
	
	DECLARE @COUNT int
	DECLARE @TSQL nvarchar(max)
	DECLARE @INNERSQL nvarchar(max)
	SET @TSQL = 'SELECT COALESCE(x.Entity1ID, ab.EntityID,aa.EntityID) as Entity1ID, Entity2ID, SHORTRELATIONSHIPNAME FROM ('
	Open ExternalViewsCursor
	Fetch NEXT FROM ExternalViewsCursor INTO  @EXTVIEW, @RELNAME
	WHILE @@FETCH_STATUS = 0 BEGIN
		SET @COUNT=0
		SET @COMBINEDVIEWS = 'SELECT * FROM ('
		WHILE @@FETCH_STATUS = 0 AND @COUNT<5 BEGIN
			IF (CHARINDEX('ENTITY1ID',@EXTVIEW)>0) BEGIN
				SET @COMBINEDVIEWS = @COMBINEDVIEWS + 'SELECT '''''''' AS SCHOOLID, '''''''' AS PERSONID, CAST(ENTITY1ID AS VARCHAR2(50)) AS ENTITY1ID, CAST(ENTITY2ID AS VARCHAR2(50)) AS ENTITY2ID, '''''+@RELNAME
				+ ''''' AS SHORTRELATIONSHIPNAME FROM (' + REPLACE(@EXTVIEW,'''', '''''') + ') UNION '
			END
			ELSE 
			IF (CHARINDEX('PERSONID',@EXTVIEW)>0) BEGIN
				SET @COMBINEDVIEWS = @COMBINEDVIEWS + 'SELECT '''''''' AS SCHOOLID, CAST(PERSONID AS VARCHAR2(30)) AS PERSONID, '''''''' AS ENTITY1ID, CAST(ENTITY2ID AS VARCHAR2(50)) AS ENTITY2ID, '''''+@RELNAME
				+ ''''' AS SHORTRELATIONSHIPNAME FROM (' + REPLACE(@EXTVIEW,'''', '''''') + ') UNION '
			END
			ELSE 
			IF (CHARINDEX('SCHOOLID',@EXTVIEW)>0) BEGIN
				SET @COMBINEDVIEWS = @COMBINEDVIEWS + 'SELECT CAST(SCHOOLID AS VARCHAR2(30)) AS SCHOOLID, '''''''' AS PERSONID, '''''''' AS ENTITY1ID, CAST(ENTITY2ID AS VARCHAR2(50)) AS ENTITY2ID, '''''+@RELNAME
				+ ''''' AS SHORTRELATIONSHIPNAME FROM (' + REPLACE(@EXTVIEW,'''', '''''') + ') UNION '
			END
			
			SET @COUNT = @COUNT + 1
			
			Fetch NEXT FROM ExternalViewsCursor INTO  @EXTVIEW, @RELNAME
			
		END
		SET @COMBINEDVIEWS = SUBSTRING(@COMBINEDVIEWS,0,LEN(@COMBINEDVIEWS)-6) + ')'
		SELECT @INNERSQL = 'SELECT * FROM OPENQUERY(PSPRODSERVER,''' + @COMBINEDVIEWS + ')'')'
		SET @TSQL = @TSQL + @INNERSQL + ' UNION '
	END
	SET @TSQL = SUBSTRING(@TSQL,0,LEN(@TSQL)-6) +
	 ')) x ' +
	 'LEFT JOIN (SELECT Attributes.* FROM Attributes JOIN DefinedAttributes da ON Attributes.AttributeID=da.AttributeID WHERE da.AttributeName=''PersonID'') ab ON x.PERSONID=ab.AttributeValueString' +
	' LEFT JOIN (SELECT Attributes.* FROM Attributes JOIN DefinedAttributes da ON Attributes.AttributeID=da.AttributeID WHERE da.AttributeName=''SchoolID'') aa ON x.SCHOOLID=ab.AttributeValueString'

	SELECT @TSQL 
	CLose ExternalViewsCursor
	DEALLOCATE ExternalViewsCursor

END